﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_WMG005:DA_Matrix
    {
        DataTable dt = null;
        string v1 = ",TO_CHAR(SUM(A.ACT_CUR_BAL)/100000000, 'FM999,999.99999999')  AS MONTH_TOT   ";
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_snapYM"></param>
        /// <param name="p_date"></param>
        /// <param name="p_opt">1:定存2:定儲</param>
        /// <returns></returns>
        public DataTable GetSummaryData(string p_snapYM,string p_date,int p_opt)
        {
            string sql;
            sql = string.Format(p_opt.Equals(1) ? getSQLStatement()
                :getSavingsStatement(), p_snapYM, p_date,v1);
            dt = getData(sql, "DEPOSIT", DBOpt.Oracle);
            return dt;
        }

        /// <summary>
        /// 給外幣使用
        /// </summary>
        /// <param name="p_snapYM">YYYYMM</param>
        /// <param name="p_date">'2011/12/1'</param>
        /// <param name="p_cur">USD</param>
        /// <returns></returns>
        public DataTable GetSummaryData(string p_snapYM,string p_date, string p_cur)
        {
            //
            if (string.Empty.Equals(p_cur))
            {
                DataSet ds = GetCriteria();
                DataTable curDT = ds.Tables["CUR"];
                StringBuilder sb = new StringBuilder();
                foreach (DataRow row in curDT.Rows)
                {
                    sb.AppendFormat(",'{0}'", row[0].ToString());
                }
                p_cur = string.Format("AND A.CUR IN ({0})"
                    , sb.ToString().Substring(1));
            }
            else
            {
                p_cur = string.Format("AND A.CUR = '{0}' ", p_cur);
            }
            string sql;
            sql = string.Format(getCURSQLStetement()
                , p_snapYM, p_cur, p_date, v1);
            dt = getData(sql,"CUR",DBOpt.Oracle);
            return dt;
        }

        public DataTable GetTrendData(string[] p_interval,DateTime p_endDate)
        {
            dt = null;
            string sql;
            sql = string.Format(getTrendStatement(), p_interval[0]
                , p_interval[1] , getTwelveMonth(p_endDate));
            dt =getData(sql, "trend", DBOpt.MSSQL);
            return dt;
        }

        public IQueryable<string> GetDistinctDueDate()
        {
            IQueryable<string> query = (from due in dt.AsEnumerable()
                select due.Field<string>("DUE_DATE")).AsQueryable().Distinct();
            return query;
        }

        public IQueryable<DataRow> GetSelectRow(string p_dueDate)
        {
            //IQueryable<DataRow> query = (from acc in dt.AsEnumerable()
            //    where acc.Field<string>("DUE_DATE").ToString().Equals(p_dueDate)
            //    select acc).AsQueryable();
            //return query;
            return getSelectRow("DUE_DATE", p_dueDate);
        }

        public IQueryable<DataRow> GetSelectRow(string p_dueDate,string p_rate)
        {
            IQueryable<DataRow> query = (from due in dt.AsEnumerable()
                                         where due.Field<string>("DUE_DATE").ToString().Equals(p_dueDate)
                                         && due.Field<string>("RATE").ToString().Equals(p_rate)
                                         select due).AsQueryable();
            return query;
        }

        protected string getSQLStatement()
        {
            /*
               * {0} ODS 中目前資料年月EX:201104 
               * {1} 查詢資料時間之前，要查2010-04-01 之前
               *
               */
            return @"

SELECT 
	NVL(A.DUE_DATE,'合計') DUE_DATE 
	,CASE WHEN A.利率型態 = '1' THEN '固定'
		  WHEN A.利率型態 = '2' THEN '機動'
		  ELSE '小計'
	END RATE
	,A.MONTH1_2
	,A.MONTH1_2R
	,A.MONTH3_5
	,A.MONTH3_5R
	,A.MONTH6_8
	,A.MONTH6_8R
	,A.MONTH9_11
	,A.MONTH9_11R
	,A.MONTH12_23
	,A.MONTH12_23R
	,A.MONTH24_35
	,A.MONTH24_35R
	,A.MONTH36
	,A.MONTH36R
    ,A.TOTRATE
FROM (

SELECT
	 CASE WHEN A.ORD = 150 THEN '一年內到期'
	 WHEN A.ORD = 250 THEN '第二年到期'
	 WHEN A.ORD = 350 THEN '第三年到期'
	 WHEN A.ORD = 380 THEN '第四年以後到期'
	 END DUE_DATE
	,NVL(A.利率型態,'3')利率型態
    ,MAX(CASE WHEN A.MON_TYPE = '1-2' THEN MONTH_TOT END) MONTH1_2
	,MAX(CASE WHEN A.MON_TYPE = '1-2' THEN AVGRATE END) MONTH1_2R
	,MAX(CASE WHEN A.MON_TYPE = '3-5' THEN MONTH_TOT END) MONTH3_5
	,MAX(CASE WHEN A.MON_TYPE = '3-5' THEN AVGRATE END) MONTH3_5R
	,MAX(CASE WHEN A.MON_TYPE = '6-8' THEN MONTH_TOT END) MONTH6_8
	,MAX(CASE WHEN A.MON_TYPE = '6-8' THEN AVGRATE END) MONTH6_8R
	,MAX(CASE WHEN A.MON_TYPE = '9-11' THEN MONTH_TOT END) MONTH9_11
	,MAX(CASE WHEN A.MON_TYPE = '9-11' THEN AVGRATE END) MONTH9_11R
	,MAX(CASE WHEN A.MON_TYPE = '12-23' THEN MONTH_TOT END) MONTH12_23
	,MAX(CASE WHEN A.MON_TYPE = '12-23' THEN AVGRATE END) MONTH12_23R
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN MONTH_TOT END) MONTH24_35
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN AVGRATE END) MONTH24_35R
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN MONTH_TOT END) MONTH36
 	,MAX(CASE WHEN A.MON_TYPE = '36' THEN AVGRATE END) MONTH36R
    ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE
	,A.ORD
FROM(
	SELECT	
		A.利率型態
		--,to_char(SUM(A.ACT_CUR_BAL), 'FM999,999,999,999,999')  AS MONTH_TOT   
        {2}
		,A.MON_TYPE        
		,A.ORD
        ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE
        ,SUM(PAYABLE)PAYABLE
		,SUM(CASE WHEN CAL_AMT < 0 THEN 0 ELSE CAL_AMT END)CAL_AMT
	FROM(	
		SELECT		
			CASE  
				WHEN A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),12) THEN 150
				WHEN	A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),11)
					AND 
						A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),24) THEN 250
				WHEN	A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),23) 
					AND 
						A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36) THEN 350
				WHEN A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),35)  THEN 380
			END ORD
			,A.int_typ 利率型態
			,CASE
				WHEN A.prd_mm < 3 THEN '1-2' 
				WHEN A.prd_mm >= 3 AND A.prd_mm <= 5 THEN '3-5'
				WHEN A.prd_mm >= 6 AND A.prd_mm <= 8 THEN '6-8'
				WHEN A.prd_mm >= 9 AND A.prd_mm <= 11 THEN '9-11'
				WHEN A.prd_mm >= 12 AND A.prd_mm <= 23 THEN '12-23'
				WHEN A.prd_mm >= 24 AND A.prd_mm <= 35 THEN '24-35'
				WHEN A.prd_mm >= 36 THEN '36'
			END MON_TYPE
			,A.ACT_CUR_BAL
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END 
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) < 0 THEN 0
				ELSE A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a
		WHERE 1=1
		AND A.snap_yyyymm = '{0}'
		AND A.cust_class ='A'
		AND A.cust_sub_class IN ('1','5')
		AND A.CUR = 'TWD'
		AND A.SUBJ IN('124','624','125','126','626','128','145','165') 
		AND A.ACT_CUR_BAL > 0
	)A 
	GROUP BY CUBE(A.ORD,A.MON_TYPE,A.利率型態)
)A GROUP BY A.ORD,A.利率型態


UNION ALL

SELECT
	A.DUE_DATE
	,NVL(A.利率型態,'3')利率型態
	,MAX(CASE WHEN A.MON_TYPE = '1-2' THEN MONTH_TOT END) MONTH1_2
	,MAX(CASE WHEN A.MON_TYPE = '1-2' THEN AVGRATE END) MONTH1_2R
	,MAX(CASE WHEN A.MON_TYPE = '3-5' THEN MONTH_TOT END) MONTH3_5	
	,MAX(CASE WHEN A.MON_TYPE = '3-5' THEN AVGRATE END) MONTH3_5R
	,MAX(CASE WHEN A.MON_TYPE = '6-8' THEN MONTH_TOT END) MONTH6_8	
	,MAX(CASE WHEN A.MON_TYPE = '6-8' THEN AVGRATE END) MONTH6_8R
	,MAX(CASE WHEN A.MON_TYPE = '9-11' THEN MONTH_TOT END) MONTH9_11	
	,MAX(CASE WHEN A.MON_TYPE = '9-11' THEN AVGRATE END) MONTH9_11R
	,MAX(CASE WHEN A.MON_TYPE = '12-23' THEN MONTH_TOT END) MONTH12_23	
	,MAX(CASE WHEN A.MON_TYPE = '12-23' THEN AVGRATE END) MONTH12_23R
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN MONTH_TOT END) MONTH24_35	
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN AVGRATE END) MONTH24_35R
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN MONTH_TOT END) MONTH36
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN AVGRATE END) MONTH36R
    ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE
    ,CASE WHEN A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),12),'YYYYMM') THEN 100
        WHEN A.DUE_DATE > TO_CHAR(ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),11),'YYYYMM')
          	AND 
					   A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),24),'YYYYMM') THEN 200
        WHEN	A.DUE_DATE > TO_CHAR(ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),23),'YYYYMM') 
					AND 
						A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36),'YYYYMM') THEN 300
		ELSE 400 END ORD
FROM(
	SELECT	
		A.利率型態
		--,to_char(SUM(A.ACT_CUR_BAL), 'FM999,999,999,999,999')  AS MONTH_TOT   
        {2}
		,A.DUE_DATE
		,A.MON_TYPE
       ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE
		,SUM(PAYABLE)PAYABLE
		,SUM(CASE WHEN CAL_AMT < 0 THEN 0 ELSE CAL_AMT END)CAL_AMT
		,GROUPING_ID(A.DUE_DATE,A.利率型態) GRP_ID
	FROM(	
		SELECT		
			CASE  
				WHEN A.DUE_DATE < TO_DATE('{1}','YYYY-MM-DD') THEN '000000'
                WHEN A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),-1)
				    AND 
					A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36) THEN TO_CHAR(A.DUE_DATE,'YYYYMM')				
			END DUE_DATE
			,A.int_typ 利率型態			
			,CASE
				WHEN A.prd_mm < 3 THEN '1-2' 
				WHEN A.prd_mm >= 3 AND A.prd_mm <= 5 THEN '3-5'
				WHEN A.prd_mm >= 6 AND A.prd_mm <= 8 THEN '6-8'
				WHEN A.prd_mm >= 9 AND A.prd_mm <= 11 THEN '9-11'
				WHEN A.prd_mm >= 12 AND A.prd_mm <= 23 THEN '12-23'
				WHEN A.prd_mm >= 24 AND A.prd_mm <= 35 THEN '24-35'
				WHEN A.prd_mm >= 36 THEN '36'
			END MON_TYPE
			,A.ACT_CUR_BAL
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END 
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) < 0 THEN 0
				ELSE A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a
		WHERE 1=1
		AND A.snap_yyyymm = '{0}'
		AND A.cust_class ='A'
		AND A.cust_sub_class IN ('1','5')
		AND A.CUR = 'TWD'
		AND A.SUBJ IN('124','624','125','126','626','128','145','165') 
		AND A.ACT_CUR_BAL > 0
	)A 
	GROUP BY CUBE(A.DUE_DATE,A.MON_TYPE,A.利率型態)
)A GROUP BY A.DUE_DATE,A.利率型態
)A
WHERE A.ORD < 500
ORDER BY A.ORD,A.DUE_DATE,A.利率型態

";
        }      

        /// <summary>
        /// 定儲
        /// </summary>
        /// <returns></returns>
        protected string getSavingsStatement()
        {
            return @"
SELECT 
	NVL(A.DUE_DATE,'合計') DUE_DATE 
	,CASE WHEN A.利率型態 = '1' THEN '固定'
		  WHEN A.利率型態 = '2' THEN '機動'
		  ELSE '小計'
	END RATE
	,A.MONTH1_2
	,A.MONTH1_2R
	,A.MONTH24_35
	,A.MONTH24_35R
	,A.MONTH36
	,A.MONTH36R
    ,A.TOTRATE
FROM (

SELECT
	 CASE WHEN A.ORD = 150 THEN '一年內到期'
	 WHEN A.ORD = 250 THEN '第二年到期'
	 WHEN A.ORD = 350 THEN '第三年到期'
	 WHEN A.ORD = 380 THEN '第四年以後到期'
	 END DUE_DATE
	,NVL(A.利率型態,'3')利率型態
	,MAX(CASE WHEN A.MON_TYPE = '1-23' THEN MONTH_TOT END) MONTH1_2
	,MAX(CASE WHEN A.MON_TYPE = '1-23' THEN AVGRATE END) MONTH1_2R
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN MONTH_TOT END) MONTH24_35	
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN AVGRATE END) MONTH24_35R
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN MONTH_TOT END) MONTH36
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN AVGRATE END) MONTH36R
    ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE
	,A.ORD
FROM(	
	SELECT	
		A.利率型態
		--,to_char(SUM(A.ACT_CUR_BAL), 'FM999,999,999,999,999')  AS MONTH_TOT   
        {2}
		,A.MON_TYPE        
		,A.ORD
        ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE
        ,SUM(PAYABLE)PAYABLE
		,SUM(CAL_AMT)CAL_AMT
	FROM(
		SELECT		
			CASE  
				WHEN A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),12) THEN 150
				WHEN	A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),11)
					AND 
						A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),24) THEN 250
				WHEN	A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),23) 
					AND 
						A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36) THEN 350
				WHEN A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),35)  THEN 380
			END ORD
			,A.int_typ 利率型態
			,CASE
				WHEN A.prd_mm < 24 THEN '1-23' 
				WHEN A.prd_mm >= 24 AND A.prd_mm <= 35 THEN '24-35'
				WHEN A.prd_mm >= 36 THEN '36'
			END MON_TYPE
			,A.ACT_CUR_BAL
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END 
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE                
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')- 1 - A.BK_VALUE + 1) < 0 THEN 0
					ELSE A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a
		WHERE 1=1
		AND A.snap_yyyymm = '{0}'
		AND A.cust_class ='A'
		AND A.cust_sub_class IN ('1','5')
		AND A.CUR = 'TWD'
		AND A.SUBJ IN('234','634','236','636','237'
				,'244','246','247','647','254','654','256','656')
		AND A.ACT_CUR_BAL > 0
)A 
	GROUP BY CUBE(A.ORD,A.MON_TYPE,A.利率型態)
)A GROUP BY A.ORD,A.利率型態


UNION ALL

SELECT
	A.DUE_DATE
	,NVL(A.利率型態,'3')利率型態
	,MAX(CASE WHEN A.MON_TYPE = '1-23' THEN MONTH_TOT END) MONTH1_2
	,MAX(CASE WHEN A.MON_TYPE = '1-23' THEN AVGRATE END) MONTH1_2R
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN MONTH_TOT END) MONTH24_35	
	,MAX(CASE WHEN A.MON_TYPE = '24-35' THEN AVGRATE END) MONTH24_35R
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN MONTH_TOT END) MONTH36
	,MAX(CASE WHEN A.MON_TYPE = '36' THEN AVGRATE END) MONTH36R
	,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE
    ,CASE WHEN A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),12),'YYYYMM') THEN 100
        WHEN A.DUE_DATE > TO_CHAR(ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),11),'YYYYMM')
          	AND 
					   A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),24),'YYYYMM') THEN 200
        WHEN	A.DUE_DATE > TO_CHAR(ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),23),'YYYYMM') 
					AND 
						A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36),'YYYYMM') THEN 300
		ELSE 400 END ORD
FROM(

	SELECT	
		A.利率型態
		--,to_char(SUM(A.ACT_CUR_BAL), 'FM999,999,999,999,999')  AS MONTH_TOT   
        {2}
		,A.DUE_DATE
		,A.MON_TYPE
		,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE
		,SUM(PAYABLE)PAYABLE
		,SUM(CAL_AMT)CAL_AMT
		,GROUPING_ID(A.DUE_DATE,A.利率型態) GRP_ID
	FROM(	
		SELECT		
			CASE  
				WHEN A.DUE_DATE < TO_DATE('{1}','YYYY-MM-DD') THEN '000000'
				WHEN A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{1}','YYYY-MM-DD')),-1)
					AND 
					A.DUE_DATE < ADD_MONTHS(TO_DATE('{1}','YYYY-MM-DD'),36) THEN TO_CHAR(A.DUE_DATE,'YYYYMM')				
			END DUE_DATE
			,A.int_typ 利率型態			
			,CASE
				WHEN A.prd_mm < 24 THEN '1-23' 
				WHEN A.prd_mm >= 24 AND A.prd_mm <= 35 THEN '24-35'
				WHEN A.prd_mm >= 36 THEN '36'
			END MON_TYPE
			,A.ACT_CUR_BAL
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END 
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) < 0 THEN 0
				ELSE A.OPN_DPR_AMT  * (TO_DATE('{1}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a
		WHERE 1=1
		AND A.snap_yyyymm = '{0}'
		AND A.cust_class ='A'
		AND A.cust_sub_class IN ('1','5')
		AND A.CUR = 'TWD'
		AND A.SUBJ IN('234','634','236','636','237'
				,'244','246','247','647','254','654','256','656')
		AND A.ACT_CUR_BAL > 0
	)A GROUP BY CUBE(A.DUE_DATE,A.MON_TYPE,A.利率型態)	
)A GROUP BY A.DUE_DATE,A.利率型態	
)A
WHERE A.ORD < 500
ORDER BY A.ORD,A.DUE_DATE,A.利率型態
";
        }

        protected string getTrendStatement()
        {
            /*
             * {0}→日期區間
             * {1}→MAX(CASE WHEN A.年月='201104' THEN A.TOT END) [201104]
             * MAX(CASE WHEN A.年月='201105' THEN A.TOT END) [201105]
             */
            return @"
SELECT
	'定期' 存款類別
	,CASE WHEN A.利率型態 = '1' THEN '固定'  ELSE '機動' END [利率型態]
	{2}
FROM(
SELECT 
	A.年月
	,A.利率型態
	,ROUND(A.BAL / B.BAL,2) AS TOT
FROM (
SELECT
	A.年月
	,A.利率型態
	,SUM(A.AVG_BAL) BAL
FROM WMG.dbo.WMG001 A
WHERE A.年月 BETWEEN '{0}' AND '{1}'
AND A.存款類別 = '定期存款'
GROUP BY A.年月,A.利率型態
) A LEFT OUTER JOIN (
					SELECT
						A.年月
						,SUM(A.AVG_BAL) BAL
					FROM WMG.dbo.WMG001 A
					WHERE A.年月 BETWEEN '{0}' AND '{1}'
					AND A.存款類別 = '定期存款'
					GROUP BY A.年月
)B ON A.年月 = B.年月
)A GROUP BY	A.利率型態


UNION ALL

SELECT
	'定儲' 存款類別
	,CASE WHEN A.利率型態 = '1' THEN '固定'  ELSE '機動' END [利率型態]
	{2}
FROM(
SELECT 
	A.年月
	,A.利率型態
	,ROUND(A.BAL / B.BAL,2) AS TOT
FROM (
SELECT
	A.年月
	,A.利率型態
	,SUM(A.AVG_BAL) BAL
FROM WMG.dbo.WMG001 A
WHERE A.年月 BETWEEN '{0}' AND '{1}'
AND A.存款類別 IN ('整存整付','零存整付','存本取息')
GROUP BY A.年月,A.利率型態
) A LEFT OUTER JOIN (
					SELECT
						A.年月
						,SUM(A.AVG_BAL) BAL
					FROM WMG.dbo.WMG001 A
					WHERE A.年月 BETWEEN '{0}' AND '{1}'
					AND A.存款類別 IN ('整存整付','零存整付','存本取息')
					GROUP BY A.年月
)B ON A.年月 = B.年月
)A GROUP BY	A.利率型態

UNION ALL

SELECT
	'定期+定儲' 存款類別
	,CASE WHEN A.利率型態 = '1' THEN '固定'  ELSE '機動' END [利率型態]
    {2}
FROM(
SELECT 
	A.年月
	,A.利率型態
	,ROUND(A.BAL / B.BAL,2) AS TOT
FROM (
SELECT
	A.年月
	,A.利率型態
	,SUM(A.AVG_BAL) BAL
FROM WMG.dbo.WMG001 A
WHERE A.年月 BETWEEN '{0}' AND '{1}'
AND A.存款類別 IN ('整存整付','零存整付','存本取息','定期存款')
GROUP BY A.年月,A.利率型態
) A LEFT OUTER JOIN (
					SELECT
						A.年月
						,SUM(A.AVG_BAL) BAL
					FROM WMG.dbo.WMG001 A
                    WHERE A.年月 BETWEEN '{0}' AND '{1}'
					AND A.存款類別 IN ('整存整付','零存整付','存本取息','定期存款')
					GROUP BY A.年月
)B ON A.年月 = B.年月
)A GROUP BY	A.利率型態
";
        }

        protected string getTwelveMonth(DateTime p_end)
        {
            StringBuilder sb = new StringBuilder();
            
            for (int i = 0; i < 12; i++)
            {
                sb.AppendFormat(
                    "   ,MAX(CASE WHEN A.年月='{0}{1}' THEN A.TOT END) [{2}{1}]"
                    + Environment.NewLine
                    ,p_end.AddMonths(i).Year.ToString()
                    ,p_end.AddMonths(i).Month.ToString().Length > 1
                        ? p_end.AddMonths(i).Month.ToString() 
                        : string.Format("0{0}"
                            , p_end.AddMonths(i).Month.ToString())
                    , (p_end.AddMonths(i).Year-1911).ToString()
                );
            }
            return sb.ToString();
        }

        protected IQueryable<DataRow> getSelectRow(string p_field, string p_value)
        {
            IQueryable<DataRow> query = (from acc in dt.AsEnumerable()
                                         where acc.Field<string>(p_field).ToString().Equals(p_value)
                                         select acc).AsQueryable();
            return query;
        } 

        public DataTable GetChartData(string p_category)
        {
            DataTable chart = new DataTable();
            
            chart.Columns.Add("pos");
            chart.Columns.Add("seq");
            chart.Columns.Add("val");

            IQueryable<DataRow> query = getSelectRow("存款類別", p_category);

            //int aa = query.AsQueryable().Count();

            DataRow chartRow = null;
            int i = 0;
            
            for (int colPos = 2; colPos < dt.Columns.Count; colPos++)
            {
                chartRow = chart.NewRow();
                chartRow["pos"] = (colPos - 2).ToString();
                chartRow["seq"] = i.ToString();
                chartRow["val"] = string.Format("'{0}'"
                    ,dt.Columns[colPos].ColumnName);
                chart.Rows.Add(chartRow);
            }
            i = 1;
            foreach (DataRow row in query)
            {                
                for (int colPos = 2; colPos < dt.Columns.Count; colPos++)
                {
                    chartRow = chart.NewRow();
                    chartRow["pos"] = (colPos - 2).ToString();
                    chartRow["seq"] = i.ToString();
                    chartRow["val"] = DBNull.Value.Equals(row[colPos]) ? "0" : row[colPos].ToString();
                    chart.Rows.Add(chartRow);
                }                
                i++;
            }

            return chart;
        }

        /// <summary>
        /// 外幣用的SQL statement
        /// </summary>
        /// <returns></returns>
        protected string getCURSQLStetement()
        {
            /*
             * {0}→YYYYMM
             * {1}→CUR
             * {2}→YYYY/MM/DD
             * {3}→總數用億除
             */
            return @"
SELECT  
	NVL(A.DUE_DATE,'合計') DUE_DATE  
    ,'固定' AS RATE_TYPE
	,A.WEEK1_3 
	,A.WEEK1_3R 
	,A.MONTH1_2 
	,A.MONTH1_2R 
	,A.MONTH3_5 
	,A.MONTH3_5R 
	,A.MONTH6_8 
	,A.MONTH6_8R 
	,A.MONTH9_11 
	,A.MONTH9_11R 
	,A.YEAR_1 
	,A.YEAR_1R 
    ,A.TOTRATE 
FROM( 

SELECT 
	 CASE WHEN A.ORD = 150 THEN '一年內到期' 
	 WHEN A.ORD = 250 THEN '第二年以後到期' 
	 END DUE_DATE 
	,MAX(CASE WHEN A.MON_TYPE = '1星期~3星期' THEN MONTH_TOT END) WEEK1_3 
	,MAX(CASE WHEN A.MON_TYPE = '1星期~3星期' THEN AVGRATE END) WEEK1_3R	 
	,MAX(CASE WHEN A.MON_TYPE = '1_2' THEN MONTH_TOT END) MONTH1_2 
	,MAX(CASE WHEN A.MON_TYPE = '1_2' THEN AVGRATE END) MONTH1_2R	 
	,MAX(CASE WHEN A.MON_TYPE = '3_5' THEN MONTH_TOT END) MONTH3_5 
	,MAX(CASE WHEN A.MON_TYPE = '3_5' THEN AVGRATE END) MONTH3_5R	 
	,MAX(CASE WHEN A.MON_TYPE = '6_8' THEN MONTH_TOT END) MONTH6_8 
	,MAX(CASE WHEN A.MON_TYPE = '6_8' THEN AVGRATE END) MONTH6_8R	 
	,MAX(CASE WHEN A.MON_TYPE = '9_11' THEN MONTH_TOT END) MONTH9_11 
	,MAX(CASE WHEN A.MON_TYPE = '9_11' THEN AVGRATE END) MONTH9_11R 
	,MAX(CASE WHEN A.MON_TYPE = '一年以上' THEN MONTH_TOT END) YEAR_1 
	,MAX(CASE WHEN A.MON_TYPE = '一年以上' THEN AVGRATE END) YEAR_1R	 
    ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE 
	,A.ORD 
FROM(	 
	SELECT	 
		A.MON_TYPE
		{3}
		,A.ORD 
        ,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE 
        ,SUM(PAYABLE)PAYABLE 
		,SUM(CAL_AMT)CAL_AMT 
	FROM( 
		SELECT		 
			CASE   
				WHEN A.DUE_DATE < ADD_MONTHS(TO_DATE('{2}','YYYY-MM-DD'),12) THEN 150 				                  
				WHEN A.DUE_DATE >= ADD_MONTHS(TO_DATE('{2}','YYYY-MM-DD'),12)  THEN 250 
			END ORD 
			,CASE		 
				WHEN A.prd_mm = 0 AND A.prd_ddd < 30 THEN '1星期~3星期' 
				WHEN A.prd_mm = 0 AND A.prd_ddd >= 30  
					THEN  
				CASE 
				WHEN (A.prd_ddd / 30) >= 1 AND (A.prd_ddd / 30) < 3 THEN '1_2' 
				WHEN (A.prd_ddd / 30) >= 3 AND (A.prd_ddd / 30) < 6 THEN '3_5' 
				WHEN (A.prd_ddd / 30) >= 6 AND (A.prd_ddd / 30) < 9 THEN '6_8' 
				WHEN (A.prd_ddd / 30) >= 9 AND (A.prd_ddd / 30) < 12 THEN '9_11' 
				WHEN (A.prd_ddd / 30) >= 12  THEN '一年以上' 
						END 
				WHEN A.prd_mm < 3 THEN '1_2'  
				WHEN A.prd_mm >= 3 AND A.prd_mm <= 5 THEN '3_5' 
				WHEN A.prd_mm >= 6 AND A.prd_mm <= 8 THEN '6_8' 
				WHEN A.prd_mm >= 9 AND A.prd_mm <= 11 THEN '9_11' 
				WHEN A.prd_mm >= 12 THEN '一年以上' 
			END MON_TYPE 
			,A.ACT_CUR_BAL 
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END  
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE                 
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{2}','YYYY-MM-DD')- 1 - A.BK_VALUE + 1) < 0 THEN 0 
					ELSE A.OPN_DPR_AMT  * (TO_DATE('{2}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT 
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a 
		WHERE 1=1 
		AND A.snap_yyyymm = '{0}' 
		AND A.cust_class ='A' 
		AND A.cust_sub_class IN ('1','5') 
		{1}
		AND A.SUBJ IN('27','37') 
		AND A.ACT_CUR_BAL > 0 
		AND A.int_typ = '1' 
		)A 	GROUP BY CUBE(A.ORD,A.MON_TYPE) 
	)A GROUP BY A.ORD 

UNION ALL 

SELECT 
	A.DUE_DATE 
	
	,MAX(CASE WHEN A.MON_TYPE = '1星期~3星期' THEN MONTH_TOT END) WEEK1_3 
	,MAX(CASE WHEN A.MON_TYPE = '1星期~3星期' THEN AVGRATE END) WEEK1_3R	 
	,MAX(CASE WHEN A.MON_TYPE = '1_2' THEN MONTH_TOT END) MONTH1_2 
	,MAX(CASE WHEN A.MON_TYPE = '1_2' THEN AVGRATE END) MONTH1_2R	 
	,MAX(CASE WHEN A.MON_TYPE = '3_5' THEN MONTH_TOT END) MONTH3_5 
	,MAX(CASE WHEN A.MON_TYPE = '3_5' THEN AVGRATE END) MONTH3_5R	 
	,MAX(CASE WHEN A.MON_TYPE = '6_8' THEN MONTH_TOT END) MONTH6_8 
	,MAX(CASE WHEN A.MON_TYPE = '6_8' THEN AVGRATE END) MONTH6_8R	 
	,MAX(CASE WHEN A.MON_TYPE = '9_11' THEN MONTH_TOT END) MONTH9_11 
	,MAX(CASE WHEN A.MON_TYPE = '9_11' THEN AVGRATE END) MONTH9_11R 
	,MAX(CASE WHEN A.MON_TYPE = '一年以上' THEN MONTH_TOT END) YEAR_1 
	,MAX(CASE WHEN A.MON_TYPE = '一年以上' THEN AVGRATE END) YEAR_1R	 
	,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END TOTRATE 
    ,CASE  
    	WHEN A.DUE_DATE < TO_CHAR(ADD_MONTHS(TO_DATE('{2}','YYYY-MM-DD'),12),'YYYYMM') THEN 100 
        WHEN A.DUE_DATE >= TO_CHAR(ADD_MONTHS(TO_DATE('{2}','YYYY-MM-DD'),12),'YYYYMM') 
			THEN 200 
        ELSE 400
	END ORD 
FROM(	 
	SELECT	 
		A.DUE_DATE 
		--to_char(SUM(A.ACT_CUR_BAL), 'FM999,999,999,999,999')  AS MONTH_TOT    
		{3} 
		,A.MON_TYPE 
		,CASE WHEN SUM(CAL_AMT)=0 THEN 0 ELSE ROUND(SUM(PAYABLE) * 360 / SUM(CAL_AMT),6) END AVGRATE 
		,SUM(PAYABLE)PAYABLE 
		,SUM(CAL_AMT)CAL_AMT 
		
	FROM( 
		SELECT		 
			CASE   
				WHEN A.DUE_DATE < TO_DATE('{2}','YYYY-MM-DD') THEN '000000' 
                WHEN A.DUE_DATE > ADD_MONTHS(LAST_DAY(TO_DATE('{2}','YYYY-MM-DD')),-1) 
					AND  
					    A.DUE_DATE < ADD_MONTHS(TO_DATE('{2}','YYYY-MM-DD'),12)  
					THEN TO_CHAR(A.DUE_DATE,'YYYYMM')					 
			END DUE_DATE 
			,CASE		 
				WHEN A.prd_mm = 0 AND A.prd_ddd < 30 THEN '1星期~3星期' 
				WHEN A.prd_mm = 0 AND A.prd_ddd >= 30  
					THEN  
				CASE 
				WHEN (A.prd_ddd / 30) >= 1 AND (A.prd_ddd / 30) < 3 THEN '1_2' 
				WHEN (A.prd_ddd / 30) >= 3 AND (A.prd_ddd / 30) < 6 THEN '3_5' 
				WHEN (A.prd_ddd / 30) >= 6 AND (A.prd_ddd / 30) < 9 THEN '6_8' 
				WHEN (A.prd_ddd / 30) >= 9 AND (A.prd_ddd / 30) < 12 THEN '9_11' 
				WHEN (A.prd_ddd / 30) >= 12  THEN '一年以上' 
						END 
				WHEN A.prd_mm < 3 THEN '1_2'  
				WHEN A.prd_mm >= 3 AND A.prd_mm <= 5 THEN '3_5' 
				WHEN A.prd_mm >= 6 AND A.prd_mm <= 8 THEN '6_8' 
				WHEN A.prd_mm >= 9 AND A.prd_mm <= 11 THEN '9_11' 
				WHEN A.prd_mm >= 12 THEN '一年以上' 
			END MON_TYPE 
			,A.ACT_CUR_BAL 
			,CASE WHEN A.CUR_INT_AMT < 0 THEN 0 ELSE A.CUR_INT_AMT END  
				+ CASE WHEN A.INT_PAYABLE < 0 THEN 0 ELSE A.INT_PAYABLE END PAYABLE 
			,CASE WHEN A.OPN_DPR_AMT  * (TO_DATE('{2}','YYYY-MM-DD')- 1 - A.BK_VALUE + 1) < 0 THEN 0 
					ELSE A.OPN_DPR_AMT  * (TO_DATE('{2}','YYYY-MM-DD')-1 - A.BK_VALUE + 1) END CAL_AMT 
		FROM DM_F_VIEW.F_WM_DEPOSIT_TD_ACCT_M a 
		WHERE 1=1 
		AND A.snap_yyyymm = '{0}' 
		AND A.cust_class ='A' 
		AND A.cust_sub_class IN ('1','5') 
		{1} 
		AND A.SUBJ IN('27','37') 
		AND A.ACT_CUR_BAL > 0 
		AND A.int_typ = '1' 
		)A GROUP BY CUBE(A.DUE_DATE,A.MON_TYPE)	 
	)A GROUP BY A.DUE_DATE 

)A WHERE A.ORD < 500 
ORDER BY A.ORD,A.DUE_DATE
";
        }

        public override string[] GetDistinctField()
        {
            return new string[]{"CUR"};
        }

        public override DataSet GetCriteria()
        {
            DataSet ds = new DataSet();
            string[] curs = { "AUD","CAD","CHF","EUR","GBP","HKD","JPY"
                                ,"NZD","SEK","SGD","THB","USD","ZAR"};
            DataTable dtRm = new DataTable("CUR");
            dtRm.Columns.Add("CUR", typeof(System.String));
            foreach (string cc in curs)
            {
                DataRow row = dtRm.NewRow();
                row[0] = cc;
                dtRm.Rows.Add(row);
            }
            ds.Tables.Add(dtRm);
            return ds;
        }
    }
}
